package net.sf.rose.jdbc;

import java.util.HashMap;
import java.util.Map;

import net.sf.rose.business.bean.database.FieldMetaBean;
import net.sf.rose.business.service.DataBaseService;
import net.sf.rose.exception.SystemErrorCode;
import net.sf.rose.exception.SystemException;
import net.sf.rose.jdbc.bean.FieldBean;
import net.sf.rose.jdbc.service.DataBaseConfigService;
import net.sf.rose.jdbc.service.Service;
import net.sf.rose.util.DateUtil;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

/**
 * 功能：自动拼接SQL语句
 * @author fengjian
 * @since 2013/05/18
 */
public class QueryMapSQL extends QuerySQL {

	private static Logger logger = Logger.getLogger(QueryMapSQL.class);
	public static String DEF_SYS_SQL = "sys_sql";
	public static String DEF_SYS_TABLE = "sys_table";

	/** 数据库服务类 */
	protected Service service = null;

	public QueryMapSQL() {
	}

	public QueryMapSQL(Service service) {
		this.service = service;
	}

	/**
	 * 返回数据库操作的执行者
	 */
	public UserBean getUser(){
		return this.service.getUser();
	}

	/**
	 * 功能:拼装查找单条记录的SQL语句
	 * <p>1.一种情况：指定表名和主键名，拼接简单的单表查询语句
	 * <p>2.一种情况：提供sql语句，将调用本类的createListSql方法完成SQL语句的拼接
	 * @param data - 数据参数
	 */
	public void createLoadSql(Map<String, Object> data) {
		Object sql = data.get(DEF_SYS_SQL);
		Object table = data.get(DEF_SYS_TABLE);
		if (sql == null && table == null) {
			String message = "必须要的查询参数：SQL语句（" + DEF_SYS_SQL + "）或表名(" + DEF_SYS_TABLE + ")至少提供一个，否则不能拼接SQL文。";
			logger.error(message);
			throw new SystemException(message, SystemErrorCode.D_SQL_SEARCH_CREATE_ERROR);
		}
		if (table != null) {
			String key = null;
			Object val = null;
			list.clear();
			String tableName = table.toString();
			key = DBUtils.getKeyFieldName(tableName);
			val = data.get(key);
			StringBuilder sb = new StringBuilder("SELECT * FROM ");
			sb.append(tableName).append(" WHERE ").append(key).append("=?");
			list.add(val);
			this.sql = sb.toString();
		} else {
			this.createListSql(sql.toString(), data);
		}
		if (list.size() == 0) {
			String message = "调用数据库Load方法时至少要有一个查询条件。";
			logger.error(message);
			throw new SystemException(message, SystemErrorCode.D_SQL_SEARCH_CREATE_ERROR);
		}
	}

	/**
	 * 功能:拼装查找数据库某表符合条件的记录集合的SQL查询语句
	 * <p>1.根据表结构的定义meta，从map集合中找出相关的表字段值作为查询参数
	 * <p>2.如果字段类型是文本、数字或日期，则根据辅助判断参数拼接SQL条件语句
	 * <p>3.示例：表名：user
	 * <p>字段：userName 字符串；age 数字；birthday 日期；
	 * <p>Map<String, Object> map = new HashMap<String, Object>();
	 * <p>map.put("userName", "fgg");
	 * <p>map.put("userName_link", "like");
	 * <p>map.put("age", 20);
	 * <p>map.put("age_link", ">");
	 * <p>map.put("birthday_from", "2013-06-25");
	 * <p>map.put("birthday_to", "2014-06-25");
	 * <p>结果sql输出：select * from user where userName like ? and age>? and (birthday>=? and birthday<=?)
	 * <p>查询参数："%fgg%"，20，"2013-06-25"，"2014-06-25"
	 * @param meta  - 数据库表定义元数据
	 * @param data   - 数据参数
	 */
	protected void createSearchSqlByTableName(String tableName, Map<String, Object> data) {
		String key = null;
		Object val = null;
		list.clear();
		// 获取查询条件参数
		Map<String, FieldBean> fields = getSearchFields(tableName, data);
		StringBuilder sb = new StringBuilder("SELECT * FROM ");
		sb.append(tableName);
		DataParser paser = new DataParser();
		if (data.size() > 0) {
			sb.append(" AND ");
			for (FieldBean field : fields.values()) {
				key = field.getTable();
				val = data.get(key);
				if (val != null) {
					// 如果是字符串类型
					if (field.isStringType()) {
						Object link = data.get(key + "_link");
						if (link != null) {
							// 使用模糊查询
							sb.append(field.getField() + " like ? AND ");
							list.add("%" + val + '%');
						} else {
							sb.append(field.getField() + "=? AND ");
							list.add(val);
						}
					}
					// 如果是数字类型
					else if (field.isNumberType()) {
						Object link = data.get(key + "_link");
						if (link != null) {
							// 使用比较查询
							sb.append(field.getField() + ' ' + link.toString().charAt(0) + " ? AND ");
							list.add(val);
						} else {
							sb.append(field.getField() + "=? AND ");
							list.add(val);
						}
					} else {
						sb.append(field.getField() + "=? AND ");
						list.add(val);
					}
				}
				// 如果还是没有取到值，可能是日期查询区间条件
				else {
					// 如果是日期类型
					if (field.isDateType()) {
						// 尝试取开始日期
						String key_fr = key + "_from";
						Object frVal = data.get(key_fr);
						if (frVal != null) {
							frVal = paser.parse(field.getJavaType(), frVal, null);
						}
						// 尝试取结束日期
						String key_to = key + "_to";
						Object to_Val = data.get(key_to);
						if (to_Val != null) {
							to_Val = paser.parse(field.getJavaType(), to_Val, null);
						}
						// 拼接日期比较查询SQL语句
						if (frVal != null || to_Val != null) {
							sb.append('(');
							if (frVal != null) {
								sb.append(field.getField()).append(">=? ");
								list.add(frVal);
								if (to_Val != null) {
									sb.append(" AND ").append(field.getField()).append("<=? ");
									list.add(to_Val);
								}
							} else {
								if (to_Val != null) {
									sb.append(field.getField()).append("<=? ");
									list.add(to_Val);
								}
							}
							sb.append(") AND ");
						}
					}
				}
			}
			int start = sb.indexOf("AND ");
			if (start != sb.length() - 4) {
				sb.replace(start, start + 4, "WHERE ");
			}
			sb.delete(sb.length() - 4, sb.length());
		}
		this.sql = sb.toString();
	}

	/**
	 * 功能:从前台传入的查询参数数据集中获取有用的查询条件
	 * <p>3.示例：表名：user
	 * <p>字段：userName 字符串；age 数字；birthday 日期；
	 * <p>Map<String, Object> map = new HashMap<String, Object>();
	 * <p>map.put("user.userName", "fgg");
	 * <p>map.put("user.age", 20);
	 * <p>结果sql输出：username,age相关的字段定义
	 * @param data   - 数据参数
	 */
	protected Map<String, FieldBean> getSearchFields(String tableName, Map<String, Object> data) {
		Map<String, FieldBean> fields = DBUtils.getFields(tableName);
		Map<String, FieldBean> map = new HashMap<String, FieldBean>();
		for (String key : data.keySet()) {
			if (key.indexOf('.') != -1) {
				String[] arr = StringUtils.split(key, '.');
				Map<String, FieldBean> fieldMap = DBUtils.getFields(arr[0]);
				FieldBean field = fieldMap.get(arr[1]);
				if (field != null) {
					FieldBean f = field.clone();
					f.setTable(f.getTable() + '.' + f.getField());
					map.put(f.getField(), f);
				}
			}else{
				FieldBean field = fields.get(key);
				if (field != null) {
					FieldBean f = field.clone();
					f.setTable(f.getField());
					map.put(f.getField(), f);
				}
			}
		}
		return map;
	}

	/**
	 * 功能:拼装SQL查询语句
	 * <p>示例：String sql = "select * from user where userID=#userID# and userName=#userName#";
	 * <p>Map<String, Object> map = new HashMap<String, Object>();
	 * <p>map.put("userID", "123");
	 * <p>map.put("userName", "fgg047");
	 * <p>QueryTableSQL query = new QueryTableSQL();
	 * <p>sql = query.createListSql(sql, map);
	 * <p>sql输出：select * from user where userID=？ and userName=？
	 * <p>查询参数："123"，"fgg047"
	 * @param sql	  - SQL语句
	 * @param map	  - 参数键值列表
	 */
	public void createListSql(String sql, Map<String, Object> map) {
		String key = null;
		StringBuilder sb = new StringBuilder(sql);
		list.clear();
		int i = sb.indexOf("#");
		if (i == -1) {
			this.sql = sql;
			return;
		}
		int j = sb.indexOf("#", i + 1);
		do {
			key = sb.substring(i + 1, j);
			list.add(map.get(key));
			sb.replace(i, j + 1, "?");
			i = sb.indexOf("#");
			j = sb.indexOf("#", i + 1);
		} while (i != -1 && j != -1);
		this.sql = sb.toString();
	}

	/**
	 * 功能:拼装SQL查询语句
	 * @param tableName    - 表名
	 * @param map               - 参数键值列表
	 */
	public void createSearchSql(Map<String, Object> data) {
		Object sql = data.get(DEF_SYS_SQL);
		Object table = data.get(DEF_SYS_TABLE);
		if (sql == null && table == null) {
			String message = "必须要的查询参数：SQL语句（" + DEF_SYS_SQL + "）或表名(" + DEF_SYS_TABLE + ")至少提供一个，否则不能拼接SQL文。";
			logger.error(message);
			throw new SystemException(message, SystemErrorCode.D_SQL_SEARCH_CREATE_ERROR);
		}
		if (table != null) {
			createSearchSqlByTableName(table.toString(), data);
		} else {
			this.createListSql(sql.toString(), data);
		}
	}

	/**
	 * 功能:拼装SQL插入语句
	 * @param bean - 参数对象
	 * @throws SystemException - 如果操作失败将抛出异常
	 */
	public void createInsertSql(String tableName, Map<String, Object> map) {
		Map<String, FieldBean> fields = DBUtils.getFields(tableName);
		map = getDBData(tableName, fields, map);
		// 如果数据库表中有创建人字段
		String fieldname = DataBaseConfigService.getCreateUser();
		Object obj = fields.get(fieldname);
		if (obj != null) {
			map.put(fieldname, this.getUser().getUserID());
		}
		// 如果数据库表中有创建时间字段
		fieldname = DataBaseConfigService.getCreateTime();
		obj = fields.get(fieldname);
		if (obj != null) {
			map.put(fieldname, DateUtil.getTimestamp());
		}
		if (map.size() == 0) {
			return;
		}
		list.clear();
		Object val = null;
		StringBuffer buf = new StringBuffer();
		StringBuffer valbuf = new StringBuffer();
		buf.append("INSERT INTO ");
		buf.append(tableName);
		buf.append("(");

		valbuf.append("VALUES(");
		for (String key : map.keySet()) {
			buf.append(key);
			buf.append(",");

			valbuf.append("?,");
			val = map.get(key);
			if (val instanceof java.util.Date) {
				val = new java.sql.Timestamp(((java.util.Date) val).getTime());
			}
			list.add(val);
		}
		buf.replace(buf.length() - 1, buf.length(), ")");
		valbuf.replace(valbuf.length() - 1, valbuf.length(), ");");
		buf.append(valbuf);
		sql = buf.toString();
	}

	/**
	 * 功能:拼装SQL更新语句
	 * @param bean - 参数对象
	 * @throws SystemException - 如果除主键外没有要更新的数据，则SQL语句拼接失败，将抛出异常
	 */
	public void createUpdateSql(String tableName, Map<String, Object> map) {
		FieldMetaBean keyField = DataBaseService.getService().getTable(tableName).getPKFieldBean();
		String idkey = keyField.getName();
		String idval = map.remove(idkey).toString();
		Map<String, FieldBean> fields = DBUtils.getFields(tableName);
		map = getDBData(tableName, fields, map);
		// 如果数据库表中有更新人字段
		String fieldname = DataBaseConfigService.getUpdateUser();
		Object obj = fields.get(fieldname);
		if (obj != null) {
			map.put(fieldname, this.getUser().getUserID());
		}
		// 如果数据库表中有更新时间字段
		fieldname = DataBaseConfigService.getUpdateTime();
		obj = fields.get(fieldname);
		if (obj != null) {
			map.put(fieldname, DateUtil.getTimestamp());
		}
		if (map.size() == 0) {
			return;
		}
		StringBuffer buf = new StringBuffer();
		buf.append("UPDATE ");
		buf.append(tableName);
		buf.append(" SET ");

		list.clear();
		for (String key : map.keySet()) {
			buf.append(key).append("=?, ");
			list.add(map.get(key));
		}
		// 拼接SQL条件语句
		buf.deleteCharAt(buf.length() - 2);
		buf.append(" WHERE ");
		buf.append(idkey);
		buf.append("=?;");
		list.add(idval);
		sql = buf.toString();
	}

	/**
	 * 功能:拼装SQL删除语句
	 * @param entityClass - 返回对象类型
	 * @param id		  - 主键值
	 * @throws SystemException - 如果没有定义主键或主键值不存在，则SQL语句拼接失败，将抛出异常
	 */
	public void createDeleteSql(String tableName, Map<String, Object> map) {
		FieldMetaBean keyField = DataBaseService.getService().getTable(tableName).getPKFieldBean();
		String idkey = keyField.getName();
		String idval = map.remove(idkey).toString();
		if (idval == null) {
			return;
		}
		list.clear();
		StringBuffer buf = new StringBuffer();
		buf.append("DELETE FROM ");
		buf.append(tableName);
		buf.append(" WHERE ");
		buf.append(idkey);
		buf.append("=? ");
		list.add(idval);
		sql = buf.toString();
	}

	/**
	 * 功能:根据数据库表结构的定义从数据集中取出相关的数据并格式化成相应的数据类型
	 * <p>1.根据数据库表结构的定义从数据集中取值
	 * <p>如果根据字段名取不到值，那么根据表名+'.'+字段名尝试取值
	 * <p>2.格式化成数据库表字段对应的数据格式
	 * <p>3.示例：表名：user
	 * <p>字段：userName String；age int；birthday date；
	 * <p>Map<String, Object> data = new HashMap<String, Object>();
	 * <p>data.put("userName", "fgg");
	 * <p>map.put("user.age", 20);
	 * <p>map.put("birthday", "2013-06-25");
	 * <p>结果生成返回对象：Map<String, Object> map = new HashMap<String, Object>();
	 * <p>其中包含：userName=fgg、age=20(int格式)、birthday=2013-06-25(Timestamp格式)
	 * @param tableName - 数据库表名
	 * @param fields    - 字段元数据
	 * @param data      - 数据集合
	 * @return          返回格式化后的数据集
	 */
	protected Map<String, Object> getDBData(String tableName, Map<String, FieldBean> fields, Map<String, Object> data){
		String key = null;
		Object val = null;
		DataParser paser = new DataParser();
		Map<String, Object> map = new HashMap<String, Object>();
		for (FieldBean field : fields.values()) {
			key = field.getField();
			val = data.get(key);
			if (val == null) {
				val = data.get(tableName + '.' + key);
			}
			if (val != null) {
				map.put(key, paser.parse(field.getJavaType(), val, null));
			}
		}
		return map;
	}

}
